package com.css.bjdt_core.dnDzz.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.css.bjdt_core.dnDzz.entity.DnDw;
import com.css.bjdt_core.dnDzz.entity.DnDzz;
import com.css.bjdt_core.dnDzz.entity.ModifyDnDwInfo;
import com.css.bjdt_core.dnDzz.entity.ModifyDzzInfo;
import com.css.bjdt_core.reportPrepare.entity.RptBatch;
import com.css.bjdt_core.reportPrepare.entity.RptBatchDzzRel;
import com.css.bjdt_core.reportPrepare.service.RptDealWithService;
import com.css.bjdt_core.ryxx.entity.Dict;

@Service
public class DnDzzService {
	@Autowired
	private Dao dao;

	@Autowired
	private RptDealWithService rptDealWithService;

	/**
	 * 构建党统党组织树对象
	 */
	public List<DnDzz> buildDnDzzTreeObj(String dzzId) {
		DnDzz d = dao.fetch(DnDzz.class, Cnd.where("DZZID", "=", dzzId));
		if (d == null) {
			return null;
		}
		List<DnDzz> list = dao.query(DnDzz.class, Cnd.where("SJDZZID", "=", dzzId).and("DELETEFLAG", "=", "0"));
		List<DnDzz> resultList = list;
		//		resultList.add(d);
		if (list != null && list.size() > 0) {
			while (true) {
				List<DnDzz> list1 = new ArrayList<DnDzz>();
				for (DnDzz dndzz : list) {
					List<DnDzz> list2 = null;
					list2 = dao.query(DnDzz.class,
							Cnd.where("SJDZZID", "=", dndzz.getDzzid()).and("DELETEFLAG", "=", "0"));
					if (list2 != null && list2.size() > 0) {
						list1.addAll(list2);
					}
				}
				if (list1 != null && list1.size() > 0) {
					resultList.addAll(list1);
					list = list1;
				} else {
					break;
				}

			}
		}
		for (DnDzz dn : resultList) {
			if (dn.getSjdzzid() != null && !"".equals(dn.getSjdzzid())) {
				DnDzz dndzz = dao.fetch(DnDzz.class, Cnd.where("DZZID", "=", dn.getSjdzzid()));
				if (dndzz != null) {
					dn.setSjdzzmc(dndzz.getDzzmc());
				}
			}
		}
		resultList.add(0, d);
		return resultList;
	}

	public DnDzz getDnDzzData(String dndzzid) {
		DnDzz dndzz = dao.fetch(DnDzz.class, Cnd.where("DZZID", "=", dndzzid));
		if (dndzz.getSjdzzid() != null && !"".equals(dndzz.getSjdzzid())) {
			DnDzz dndzz1 = dao.fetch(DnDzz.class, Cnd.where("DZZID", "=", dndzz.getSjdzzid()));
			if (dndzz1 != null) {
				dndzz.setSjdzzmc(dndzz1.getDzzmc());
			}
		}

		return dndzz;
	}

	public int deleteDzz(String dzzid) {
		return dao.delete(DnDzz.class, dzzid);

	}

	public DnDzz insert(DnDzz dndzz) {
		return dao.insert(dndzz);
	}

	public int update(DnDzz dndzz) {
		return dao.update(dndzz);
	}

	public Map queryDnDzzPage(String dndzzid, int pageNum, int pageSize) {
		List<DnDzz> dzz = buildDnDzzTreeObj(dndzzid);
		Map<String, Object> result = new HashMap<String, Object>();
		result.put("Rows", dzz);
		result.put("Total", dzz.size());
		return result;
	}

	//根据ID获得党组织
	public DnDzz getDnDzzDataByDzzId(String dndzzid) {
		DnDzz dndzz = dao.fetch(DnDzz.class, Cnd.where("DZZID", "=", dndzzid));
		return dndzz;
	}

	//上级党组织ID获得下一级党组织信息，返回MAP
	public Map<String, DnDzz> getChildDnDzzReturnMap(String parentId) {
		String sqlStr = "select DZZID,DZZJC from T_M_ZZINFO where DZZID in ("
				+ " select DZZID from t_m_zzinfo t where t.SJDZZID = @dzzId and t.DELETEFLAG = '0') ";
		Sql sql = Sqls.create(sqlStr);
		sql.params().set("dzzId", parentId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				Map<String, DnDzz> res = new HashMap<>();
				while (rs.next()) {
					String key = rs.getString("DZZID");
					DnDzz dnDzz = new DnDzz();
					dnDzz.setDzzid(rs.getString("DZZID"));
					dnDzz.setDzzjc(rs.getString("DZZJC"));
					res.put(key, dnDzz);
				}
				return res;
			}
		});
		dao.execute(sql);
		return sql.getObject(Map.class);
	}

	//根据ID获得下级党组织
	public List<DnDzz> getLowerDzz(String dndzzid) {
		List<DnDzz> dndzz = dao.query(DnDzz.class,
				Cnd.where("SJDZZID", "=", dndzzid).and("DELETEFLAG", "=", "0").asc("ORDERNUM"));
		return dndzz;
	}

	//根据传入的DZZID和党组织名称，查出当前的符合该名称的党组织
	public List<DnDzz> getLowerDzzByDzzName(String parentDzzId, String name) {
		/*String sqlStr = "select DZZID,DZZMC,DZZJC,SJDZZID from t_m_zzinfo t where t.DZZID != @dzzId and DZZJC LIKE @dzzName"
				+ " start with t.dzzid = @dzzId connect by prior t.dzzid =t.sjdzzid";*/
		String sqlStr = "select DZZID,DZZMC,DZZJC,SJDZZID from t_m_zzinfo t where t.DZZJC LIKE @dzzName and t.sjdzzid = @parentDzzId and t.deleteflag = '0' ";
		Sql sql = Sqls.create(sqlStr);
		sql.params().set("parentDzzId", parentDzzId).set("dzzName", "%" + name + "%");
		sql.setCallback(new SqlCallback() {
			List<DnDzz> dzzInfoList = new ArrayList<DnDzz>();

			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				while (rs.next()) {
					DnDzz dnDzz = new DnDzz();
					dnDzz.setDzzid(rs.getString("DZZID"));
					dnDzz.setDzzjc(rs.getString("DZZJC"));
					dnDzz.setDzzmc(rs.getString("DZZMC"));
					dnDzz.setSjdzzid(rs.getString("SJDZZID"));
					dzzInfoList.add(dnDzz);
				}
				return dzzInfoList;
			}
		});
		dao.execute(sql);
		return sql.getList(DnDzz.class);
	}

	//根据ID获得下级党组织（只是136和211的）
	public List<String> getLowerDzzGy(String dndzzid) {
		//List<DnDzz> dndzz = dao.query(DnDzz.class, Cnd.where("SJDZZID", "=", dndzzid).and("zzlb", "in", "136,211"));
		//剔除所有支部，支部包括('412','413','422','423','621','631','632','711','799')
		String sqlStr = "select DZZID from t_m_zzinfo t where t.SJDZZID=@dzzId and t.zzlb in ('136','221') and t.zzlb not in ('412','413','422','423','621','631','632','711','799') and t.deleteflag = '0' ";
		Sql sql = Sqls.create(sqlStr);
		sql.params().set("dzzId", dndzzid);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> dzzIdsList = new ArrayList<String>();
				while (rs.next()) {
					dzzIdsList.add(rs.getString("DZZID"));
				}
				return dzzIdsList;
			}
		});
		dao.execute(sql);
		return sql.getList(String.class);
	}

	public List<String> getAllChildByDzzId(String dzzId) {
		//剔除所有支部，支部包括('412','413','422','423','621','631','632','711','799')
		String sqlStr = "select DZZID from t_m_zzinfo t where t.DZZID != @dzzId and t.deleteflag = '0' "
				/*20161227 修改 去掉zzlb的过滤*/
				/*+ "and t.zzlb not in ('412','413','422','423','621','631','632','711','799','311','399') "*/
				+ "start with t.dzzid = @dzzId connect by prior t.dzzid =t.sjdzzid";
		Sql sql = Sqls.create(sqlStr);
		sql.params().set("dzzId", dzzId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<String> dzzIdsList = new ArrayList<String>();
				while (rs.next()) {
					dzzIdsList.add(rs.getString("DZZID"));
				}
				return dzzIdsList;
			}
		});
		dao.execute(sql);
		return sql.getList(String.class);
	}

	//得到当前党组织的下一级的所有子节点
	public List<String> getAllChildByDzzIdNextLevel(String dzzId) {
		//剔除所有支部，支部包括('412','413','422','423','621','631','632','711','799')
		String sqlStr = "select DZZID from t_m_zzinfo t where t.SJDZZID = @dzzId and t.DELETEFLAG = '0' order by ORDERNUM asc"
		/*20161222 从sql里去掉这半句，现在也得汇总支部的数据：
		 * and t.zzlb not in ('412','413','422','423','621','631','632','711','799','311','399')*/;
		Sql sql = Sqls.create(sqlStr);
		sql.params().set("dzzId", dzzId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<String> dzzIdsList = new ArrayList<String>();
				while (rs.next()) {
					dzzIdsList.add(rs.getString("DZZID"));
				}
				return dzzIdsList;
			}
		});
		dao.execute(sql);
		return sql.getList(String.class);
	}

	//通过ids获取党组织详细信息
	public List<DnDzz> getDzzByIds(String ids) {
		if (ids.equals("")) {
			return new ArrayList<DnDzz>();
		}
		List<DnDzz> result = new ArrayList<DnDzz>();
		String[] id = ids.split(",");
		for (String did : id) {
			DnDzz dndzz = dao.fetch(DnDzz.class, Cnd.where("DZZID", "=", did));
			result.add(dndzz);
		}
		return result;
	}

	//非汇总单位
	public List<String> getAllChildByDzzIdContainBranch(String dzzId) {
		//包括所有支部
		String sqlStr = "select DZZID from t_m_zzinfo t  where t.deleteflag = '0' "
				+ "start with t.dzzid = @dzzId connect by prior t.dzzid =t.sjdzzid";
		Sql sql = Sqls.create(sqlStr);
		sql.params().set("dzzId", dzzId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<String> dzzIdsList = new ArrayList<String>();
				while (rs.next()) {
					dzzIdsList.add(rs.getString("DZZID"));
				}
				return dzzIdsList;
			}
		});
		dao.execute(sql);
		return sql.getList(String.class);
	}

	//汇总单位
	//汇总单位得到下级所有已经审核通过的党委，再根据这些党委获得他们下面的所有支部
	public List<String> getAllChildByDzzIdContainBranchSum(String dzzId, String batchId, String annualId) {
		List<RptBatchDzzRel> rptBatchDzzRelList = rptDealWithService.queryBatchDzzRel(batchId);
		List<String> dzzIdsList = new ArrayList<String>();
		List<String> dzzIdsListReturn = new ArrayList<String>();
		String sqlStr = "select DZZID from t_m_zzinfo t  where t.deleteflag = '0' "
				+ "start with t.dzzid = @dzzId connect by prior t.dzzid =t.sjdzzid";
		//这里需要把自己本单位的ID剔除出去，防止这个汇总单位一级下面有支部
		for (RptBatchDzzRel rptBatchDzzRel : rptBatchDzzRelList) {
			if (!rptBatchDzzRel.getDzzId().equals(dzzId)) {
				dzzIdsList.add(rptBatchDzzRel.getDzzId());
			}
		}
		for (String dzzIdChild : dzzIdsList) {
			//先判断这个单位的这个版本下的报表是否已经审批通过
			RptBatchDzzRel rptBatchDzzRel = rptDealWithService.getARptBatchDzzRel(dzzIdChild, annualId);
			RptBatch rptBatch = rptDealWithService.queryBatch(rptBatchDzzRel.getId());
			if (rptBatch.equals("3")) {
				Sql sql = Sqls.create(sqlStr);
				sql.params().set("dzzId", dzzIdChild);
				sql.setCallback(new SqlCallback() {
					@Override
					public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
						List<String> dzzIdsListConn = new ArrayList<String>();
						while (rs.next()) {
							dzzIdsListConn.add(rs.getString("DZZID"));
						}
						return dzzIdsListConn;
					}
				});
				dao.execute(sql);
				dzzIdsListReturn.addAll(sql.getList(String.class));
			} else {
				continue;
			}
		}
		return dzzIdsListReturn;
	}

	public List<DnDzz> getAllInitiatedDescendantsByDzzId(String dzzId, String annualId) {
		String sqlStr = "select * " + "from T_M_ZZINFO z " + "  where z.deleteflag = '0'  START WITH z.DZZID=@dzzId "
				+ "CONNECT BY PRIOR z.DZZID=z.SJDZZID AND EXISTS " + "(SELECT 1 from RPT_UNIT_ANNUAL_R r "
				+ "WHERE r.DZZ_ID=z.DZZID AND r.ANNUAL_ID=@annualId) " + " ORDER BY z.ORDERNUM";
		Sql sql = Sqls.queryEntity(sqlStr);
		sql.params().set("dzzId", dzzId).set("annualId", annualId);
		sql.setEntity(dao.getEntity(DnDzz.class));
		dao.execute(sql);
		return sql.getList(DnDzz.class);
	}

	public String getLowerDzzCount(String dndzzid) {
		String sqlStr = "select count(1) from T_M_ZZINFO z where z.SJDZZID = @dzzId and z.DELETEFLAG = '0'";
		Sql sql = Sqls.create(sqlStr);
		sql.params().set("dzzId", dndzzid);
		sql.setCallback(Sqls.callback.integer());
		dao.execute(sql);
		return sql.getObject(Integer.class) + "";
	}

	public List<DnDzz> getAllChildByDzzIdNextLevelEntity(String dzzId) {
		//剔除所有支部，支部包括('412','413','422','423','621','631','632','711','799')
		String sqlStr = "select DZZID,DZZJC from t_m_zzinfo t where t.SJDZZID = @dzzId and t.DELETEFLAG = '0' order by ORDERNUM asc"
		/*20161222 从sql里去掉这半句，现在也得汇总支部的数据：
		 * and t.zzlb not in ('412','413','422','423','621','631','632','711','799','311','399')*/;
		Sql sql = Sqls.create(sqlStr);
		sql.params().set("dzzId", dzzId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<DnDzz> dzzIdsList = new ArrayList<DnDzz>();
				while (rs.next()) {
					DnDzz dnDzz = new DnDzz();
					dnDzz.setDzzid(rs.getString("DZZID"));
					dnDzz.setDzzjc(rs.getString("DZZJC"));
					dzzIdsList.add(dnDzz);
				}
				return dzzIdsList;
			}
		});
		dao.execute(sql);
		return sql.getList(DnDzz.class);
	}

	//add by qiyuxiong 20170905 统计当前党组下级组织的报表状态 begin
	public String getLowerDzzStat(String dzzid, String state, String annualId) {
		String sqlStr = "select count(t.REPORT_STATE) from RPT_BATCH t where t.REPORT_STATE in(@state) and t.BATCH_ID in(select m.BATCH_ID from RPT_BATCH_DZZ_R m where m.DDZ_ID in(select z.DZZID from T_M_ZZINFO z where z.SJDZZID = @dzzId and z.DELETEFLAG = '0') and m.ANNUAL_ID = @annualId)";
		Sql sql = Sqls.create(sqlStr);
		sql.params().set("dzzId", dzzid);
		sql.params().set("annualId", annualId);
		String[] strs = state.split(",");
		Integer[] ints = new Integer[strs.length];
		for (int i = 0; i < strs.length; i++) {
			ints[i] = Integer.parseInt(strs[i]);
		}
		sql.params().set("state", ints);
		sql.setCallback(Sqls.callback.integer());
		dao.execute(sql);
		return sql.getObject(Integer.class) + "";
	}

	public String getLowerDzzExamineType(String dzzid, String state, String annualId) {
		String sqlStr = "select count(t.EXAMINE_TYPE) from RPT_BATCH t where t.EXAMINE_TYPE in(@state) and t.BATCH_ID in(select m.BATCH_ID from RPT_BATCH_DZZ_R m where m.DDZ_ID in(select z.DZZID from T_M_ZZINFO z where z.SJDZZID = @dzzId and z.DELETEFLAG = '0') and m.ANNUAL_ID = @annualId)";
		Sql sql = Sqls.create(sqlStr);
		sql.params().set("dzzId", dzzid);
		sql.params().set("annualId", annualId);
		String[] strs = state.split(",");
		Integer[] ints = new Integer[strs.length];
		for (int i = 0; i < strs.length; i++) {
			ints[i] = Integer.parseInt(strs[i]);
		}
		sql.params().set("state", ints);
		sql.setCallback(Sqls.callback.integer());
		dao.execute(sql);
		return sql.getObject(Integer.class) + "";
	}
	//add by qiyuxiong 20170905 统计当前党组下级组织的报表状态 end

	/**
	 * 分页查询该党组织信息
	 * @param cnd
	 * @return
	 */
	public Map queryDzzDetailxxsPage(int pageNum, int pageSize, String idLst) {
		Map<String, Object> result = new HashMap<String, Object>();
		String[] arrids = {};
		if (StringUtils.isNoneBlank(idLst)) {
			arrids = idLst.split(",");
		} else {
			return result;
		}

		String sqlStr = "	select distinct z.DZZID,z.DZZJC,z.ZZLB, z.DZZLSGX,z.sjdzzid,DW.DWID,DW.DWMC,DW.DWLSGX,DW.DWLB,"
				+ " lb.dmcpt as zlbc,zgx.dmcpt as dzzlsgxc," + "wgx.dmcpt as dwlsgxc," + " wlb.dmcpt as dwlbc "
				+ " from t_m_zzinfo z"
				+ " left  join (select * from (select r1.dzz_id,a.*, row_number() over(partition by r1.dzz_id order by a.dwlb asc) rn"
				+ " from t_dn_rel r1" + " inner join t_dn_dw a on a.dwid=r1.dw_id"
				+ " where a.qybz='Y' and  r1.qybz='Y'      )" + "  where rn=1)dw  on DW.dzz_id=z.dzzid"
				+ " left join t_dm_zb62_pzjglb  lb" + " on lb.dmcod = z.zzlb" + " left join t_dm_zb95_dzzlsgx zgx"
				+ " on zgx.dmcod = z.DZZLSGX" + " left join t_dm_zb87_dwlsgx wgx" + " on wgx.dmcod = dw.dwlsgx"
				+ " left join t_dm_dn009_dwlb wlb" + " on wlb.dmcod = dw.dwlb " + "  where z.Deleteflag=0"
				+ " AND z.dzzid in (@dzzids)";
		Sql sql = Sqls.create(sqlStr);

		//sql.params().set("dzzId", dzzId);
		sql.params().set("dzzids", arrids);
		Pager pager = dao.createPager((pageNum < 1) ? 1 : pageNum, (pageSize < 1) ? 10 : pageSize);
		sql.setPager(pager);
		sql.setCallback(new SqlCallback() {

			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<DnDzz> dnDzzList = new ArrayList<DnDzz>();
				while (rs.next()) {
					DnDzz dnDzz = new DnDzz();
					dnDzz.setDzzid(rs.getString("DZZID"));
					dnDzz.setDzzjc(rs.getString("DZZJC"));
					dnDzz.setSjdzzmc(rs.getString("SJDZZID"));
					dnDzz.setDwmc(rs.getString("DWMC"));
					dnDzz.setZlbc(rs.getString("ZLBC"));
					dnDzz.setDzzlsgxc(rs.getString("DZZLSGXC"));
					dnDzz.setDwlbc(rs.getString("DWLBC"));
					dnDzz.setDwlsgxc(rs.getString("DWLSGXC"));
					dnDzz.setDwId(rs.getString("DWID"));
					dnDzz.setSjdzzjc(dao.fetch(DnDzz.class, rs.getString("SJDZZID")).getDzzjc());

					dnDzzList.add(dnDzz);
				}
				return dnDzzList;
			}
		});

		dao.execute(sql);
		List<DnDzz> lst = sql.getList(DnDzz.class);

		result.put("Rows", lst);
		int count = arrids.length;

		result.put("Total", count);
		return result;
	}

	/**
	 * 根据数字字典的表名，获得对应的list信息.(数据字典表：性别、名族...)
	 * @param tableName
	 * @return
	 */
	public List getDisList(String tableName) {
		Sql sql = Sqls.create("select dmcod,dmcpt from $table");
		sql.vars().set("table", tableName);
		sql.setCallback(new SqlCallback() {

			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<Dict> list = new ArrayList<Dict>();
				while (rs.next()) {
					Dict obj = new Dict();
					obj.setDmcod(rs.getString("DMCOD"));
					obj.setDmcpt(rs.getString("DMCPT"));
					list.add(obj);
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(Dict.class);
	}

	public ModifyDzzInfo insertModifyDzzInfo(ModifyDzzInfo obj) {
		return dao.insert(obj);
	}

	public ModifyDzzInfo getModifyDzzInfo(String dzzId) {
		ModifyDzzInfo modifyDzzInfo = dao.fetch(ModifyDzzInfo.class, Cnd.where("dzzId", "=", dzzId));

		return modifyDzzInfo;
	}

	//根据单位ID得到一个单位的基本信息
	public DnDw getADnDwByDwid(String dwId) {
		DnDw dnDw = dao.fetch(DnDw.class, Cnd.where("DWID", "=", dwId));
		return dnDw;
	}

	public void updateADndwById(String dwId, String dwmc, String frbz, String dwjc, String dwlb, String dwlsgx,
			String jczzqk) {
		String strSql = "update T_DN_DW set DWMC = @dwmc, FRBZ = @frbz, DWJC = @dwjc, DWLB = @dwlb,DWLSGX = @dwlsgx,JCZZQK = @jczzqk where DWID = @dwId";
		Sql sql = Sqls.create(strSql);
		sql.params().set("dwmc", dwmc).set("frbz", frbz).set("dwjc", dwjc).set("dwlb", dwlb).set("dwlsgx", dwlsgx)
				.set("jczzqk", jczzqk).set("dwId", dwId);
		dao.execute(sql);

		if (!isHaveDwId(dwId)) {
			ModifyDnDwInfo modifyDnDwInfo = new ModifyDnDwInfo();
			modifyDnDwInfo.setDwId(dwId);
			modifyDnDwInfo.setMstatus("1");
			dao.insert(modifyDnDwInfo);
		}
	}

	public boolean isHaveDwId(String dwId) {
		ModifyDnDwInfo modifyDnDwInfo = dao.fetch(ModifyDnDwInfo.class, Cnd.where("DWID", "=", dwId));
		if (modifyDnDwInfo != null) {
			return true;
		} else {
			return false;
		}
	}
}
